package initDB.initH2.dbConnection;

import java.sql.*;

import org.h2.jdbcx.JdbcConnectionPool;

/**
 * 连接h2数据库
 * @author psp
 *
 */
public class DBConnection {
	/*
	 * By default, if the database specified in the URL does not yet exist,
	 * a new (empty) database is created automatically. The user that
	 * created the database automatically becomes the administrator of this database. 
	 * 
	 * java -cp h2-1.4.193.jar org.h2.tools.Server可启动h2或双击jar包
	 * 拷贝database: java -cp h2-1.4.193.jar org.h2.tools.Script -url jdbc:h2:~/test -user "sa" 
	 * 查看帮助命令 java -cp h2-1.4.193.jar org.h2.tools.Backup -?
	 * 
	 * ------------------------------- 
	 * 打开sql命令行:java -cp h2-1.4.193.jar org.h2.tools.Shell(show tables,show databases等命令)
	 * 
	 */

	public static void connectionPool() throws Exception {
		JdbcConnectionPool cp = JdbcConnectionPool.create("jdbc:h2:~/test", "", "");
		for (int i = 0; i < 10; i++) {
			Connection conn = cp.getConnection();
			conn.createStatement().execute("DROP TABLE IF EXISTS TEST;CREATE TABLE TEST(ID INT PRIMARY KEY,NAME VARCHAR(255));INSERT INTO TEST VALUES(1, 'Hello');");

			conn.close();
		}
		cp.dispose();
	}
	
	public static Connection getConnection() throws Exception {
		Class.forName("org.h2.Driver");
		//Connection conn = DriverManager.getConnection("jdbc:h2:~/test", "sa", "");
		//Connection conn = DriverManager.getConnection("jdbc:h2:mem:test", "sa", "");
		Connection conn = DriverManager.getConnection("jdbc:h2:tcp://localhost/~/test", "sa", "");
		
		return conn;
	}

	public static void main(String[] a) throws Exception {
		Class.forName("org.h2.Driver");
		Connection conn = DriverManager.getConnection("jdbc:h2:mem:test", "sa", "");
		System.out.println(conn);
		Statement stmt = conn.createStatement();
		stmt.execute("DROP TABLE IF EXISTS TEST;CREATE TABLE TEST(ID INT,NAME VARCHAR(255));INSERT INTO TEST VALUES(1, 'Hello');");
		
		ResultSet rset = stmt.executeQuery("select * from test");
		while (rset.next()) {
			System.out.println(rset.getString(1));
			System.out.println(rset.getString(2));
		}
		conn.close();
		
	}
}
